﻿using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFTeach
{
    class Program
    {
        static void Main(string[] args)
        {

            //AddModel();

            //ModifyModel1();

            //ModifyModel2();

            //DeleteModel1();

            //DeleteModel2();

            //QueryModel();


            //LingToString();
            //LingToList();

            //StandardMethod1();
            //StandardMethod2();
            //DataProjection();
            //DataProjectionToList();

            //MultQuery();
            //NestQuery();
            //Console.WriteLine("-------------------------------");
            //SubQuery();

            //SelectSubQuery();

            //StateTracking1();
            //StateTracking2();
            //StateTracking3();

            //AddStudent();
            //UpdateStudent();
            //DeleteStudent();

            //QueryStudent();
            //ExecuteNonQuerySql();

            //ExecuteQuerySql();
            //ExecuteStoredProcedure();
            //ExecuteQuerySql();

            Console.Read();
        }

        #region EF第1次课：EF的基础CRUD
        static void AddModel()
        {
            //验证数据...


            //【1】对象封装
            Students student = new Students
            {
                StudentAddress = "天津",
                StudentName = "喜科堂的全栈VIP学员",
                StudentIdNo = "120224199012121819",
                Age = 29,
                DateOfBirth = Convert.ToDateTime("1990-12-12"),
                ClassId = 2,
                Gender = "男",
                PhoneNumber = "18900000000"
            };

            //【2】创建DB上下文对象
            EFDBEntities db = new EFDBEntities();
            db.Students.Add(student);//将对象添加到缓存中
            int result = db.SaveChanges();//更新到数据库（自动生成SQL语句）

            Console.WriteLine("执行结果：" + result);
            Console.WriteLine("当前最大的学号（标识列）：" + student.StudentId);
        }

        //修改对象：方式1，需要修改全部的字段值
        static void ModifyModel1()
        {
            //验证数据...


            //【1】对象封装
            Students student = new Students
            {
                StudentAddress = "北京",
                StudentName = "高级进阶VIP学员",
                StudentIdNo = "120224199012121820",
                Age = 27,
                DateOfBirth = Convert.ToDateTime("1992-12-12"),
                ClassId = 2,
                Gender = "男",
                PhoneNumber = "18900000001",
                StudentId = 100010  //修改必须要有的
            };

            //【2】创建DB上下文对象
            EFDBEntities db = new EFDBEntities();
            db.Students.Attach(student);  //附加
            db.Entry(student).State = System.Data.Entity.EntityState.Modified;//设置对象的状态为修改

            int result = db.SaveChanges();

            Console.WriteLine("执行结果：" + result);

        }

        //修改对象：方式2，实现部分字段的修改，但是要求先查询
        static void ModifyModel2()
        {

            EFDBEntities db = new EFDBEntities();

            //修改之前，我们要找到修改的实体
            var student = db.Students.FirstOrDefault(s => s.StudentId == 100010);

            //修改对象属性
            student.StudentName = "优秀学员";
            student.PhoneNumber = "13000000000";

            //提交修改        
            int result = db.SaveChanges();

            Console.WriteLine("执行结果：" + result);

        }

        //删除对象：方式1，先查询，再删除
        static void DeleteModel1()
        {

            EFDBEntities db = new EFDBEntities();

            //我们要找到修改的实体
            var student = db.Students.FirstOrDefault(s => s.StudentId == 100010);

            //从缓存中移除对象
            db.Students.Remove(student);

            //提交     
            int result = db.SaveChanges();

            Console.WriteLine("执行结果：" + result);
        }

        //删除对象：方式2，先查询，再删除（这个是开发中常用的）
        static void DeleteModel2()
        {
            EFDBEntities db = new EFDBEntities();

            //构建删除对象
            Students student = new Students { StudentId = 100009 };

            //附加到DBContext中，并设置删除窗体
            db.Set<Students>().Attach(student);
            db.Entry(student).State = System.Data.Entity.EntityState.Deleted;

            //提交数据库    
            int result = db.SaveChanges();

            Console.WriteLine("执行结果：" + result);
        }

        //查询对象
        static void QueryModel()
        {
            EFDBEntities db = new EFDBEntities();

            //定义查询
            var stuList = from s in db.Students
                          where s.StudentId > 100005
                          select s;

            Console.WriteLine(stuList); //输出SQL语句
            Console.WriteLine("---------------------------------------");

            //真正的执行查询
            foreach (var item in stuList)
            {
                Console.WriteLine($"{item.StudentId}\t{item.Age}\t{item.StudentName}");
            }
        }
        #endregion


        #region EF第2次课：EF高级查询和性能优化

        #region Linq to Object & 查询语句VS查询方法

        //Linq to string
        static void LingToString()
        {
            string strTest = "We are studying Linq To Objects";

            //查询语句
            var query1 = from c in strTest
                         select c;
            foreach (var c in query1)
            {
                Console.WriteLine(c);
            }

            Console.WriteLine("------------------------------");

            //查询方法
            var query2 = strTest.Where(c => char.IsUpper(c));
            foreach (var c in query2)
            {
                Console.WriteLine(c);
            }
        }

        //Linq to List<T>       
        static void LingToList()
        {
            List<Students> students = new List<Students>
                    {
                         new Students(){ StudentName="张某某",Gender="男",Age=21},
                         new Students(){ StudentName="李某某",Gender="女",Age=23},
                         new Students(){ StudentName="张小某",Gender="男",Age=20}
                    };
            //查询语句：查询性别为男,姓氏为张的学生，并且按照年龄升序排列
            var stuList1 = from stu in students
                           where stu.StudentName.StartsWith("张") && stu.Gender == "男" //StartsWith以张开头
                           orderby stu.Age ascending
                           select stu;

            //查询语句+查询方法
            var stuList2 = (from stu in students
                            where stu.StudentName.StartsWith("张") && stu.Gender == "男"
                            orderby stu.Age ascending
                            select stu).Count();

            foreach (var item in stuList1)
            {
                Console.WriteLine(item.StudentName + "\t" + item.Age);
            }
            Console.WriteLine("满足条件总数：" + stuList2);
            Console.WriteLine("------------------------------");

            //查询方法
            var stuList3 = students
                .Where(stu => stu.StudentName.StartsWith("张") & stu.Gender.Equals("男"))
                .OrderBy(stu => stu.Age);

            foreach (var item in stuList3)
            {
                Console.WriteLine(item.StudentName + "\t" + item.Age);
            }
        }

        #endregion

        #region 规范函数的使用

        //规范函数的使用（注意个别出现的问题）
        static void StandardMethod1()
        {
            EFDBEntities db = new EFDBEntities();

            //查询语句：查询性别为男,姓氏为张的学生，并且按照年龄升序排列
            var stuList1 = from stu in db.Students
                           where stu.StudentName.StartsWith("喜科堂") && stu.Gender == "男"
                           && stu.DateOfBirth > Convert.ToDateTime("1990-01-01")
                           orderby stu.Age ascending
                           select stu;
            Console.WriteLine("查询结果总数：" + stuList1.Count());

        }
        //规范函数中，非规范函数的解决
        static void StandardMethod2()
        {
            EFDBEntities db = new EFDBEntities();
            DateTime dateOfBirth = Convert.ToDateTime("1990-01-01");

            //查询语句：查询性别为男,姓氏为张的学生，并且按照年龄升序排列
            var stuList1 = from stu in db.Students
                           where stu.StudentName.StartsWith("喜科堂") && stu.Gender == "男"
                           && stu.DateOfBirth > dateOfBirth
                           orderby stu.Age ascending
                           select stu;

            Console.WriteLine(stuList1.ToString());
            Console.WriteLine("查询结果总数：" + stuList1.Count());
        }

        #endregion

        #region 数据投影与List转换

        //数据投影（匿名对象）
        static void DataProjection()
        {
            EFDBEntities db = new EFDBEntities();
            var stuList = from stu in db.Students
                          where stu.StudentName.Contains("喜科堂")
                          orderby stu.Age descending
                          select new { stu.StudentId, stu.StudentName, stu.StudentIdNo, stu.Gender, stu.Age };

            foreach (var item in stuList)
            {
                //当我们使用匿名对象的时候，匿名对象依然支持智能提示
                Console.WriteLine(item.StudentId + "\t" + item.StudentName + "\t" + item.Age);
            }
        }
        //数据投影To对象集合(是我们后面项目中用的，比如我们给大家讲的MVC+EF的酒店网站管理后台)
        //优点：性能极高，但是相对麻烦
        static void DataProjectionToList()
        {
            EFDBEntities db = new EFDBEntities();
            var stuList1 = from stu in db.Students
                           where stu.StudentName.Contains("喜科堂")
                           orderby stu.Age descending
                           select new { stu.StudentId, stu.StudentName, stu.StudentIdNo, stu.Gender, stu.Age };

            List<Students> stuList2 = new List<Students>();
            //将匿名对象转换成我们需要的明确类型的对象
            foreach (var item in stuList1)
            {
                stuList2.Add(new Students
                {
                    StudentId = item.StudentId,
                    StudentName = item.StudentName,
                    StudentIdNo = item.StudentIdNo,
                    Gender = item.Gender,
                    Age = item.Age
                });
            }
            //我们学习到这里，有几种访问数据的方法？ADO.NET-->自定义ORM-->EF的Linq查询
            foreach (var item in stuList2)
            {
                Console.WriteLine(item.StudentId + "\t" + item.StudentName + "\t" + item.Age);
            }
        }

        #endregion

        #region 导航属性查询和Join查询

        static void MultQuery()
        {
            EFDBEntities db = new EFDBEntities();
            //使用导航属性关联查询3个数据表
            var stuList1 = from s in db.ScoreList
                           where s.Students.Gender == "男"
                           select new
                           {
                               s.Students.StudentId,
                               s.Students.StudentName,
                               s.SQLServerDB,
                               s.Students.StudentClass.ClassName
                           };

            foreach (var item in stuList1)
            {
                Console.WriteLine($"{item.StudentId}\t{item.StudentName}\t{item.SQLServerDB}\t{item.ClassName}");
            }
            Console.WriteLine("-------------------------------------------------");

            var stuList2 = from score in db.ScoreList
                           join s in db.Students on score.StudentId equals s.StudentId
                           join c in db.StudentClass on s.ClassId equals c.ClassId
                           where s.Gender == "男"
                           select new { s.StudentId, s.StudentName, score.SQLServerDB, c.ClassName };

            foreach (var item in stuList2)
            {
                Console.WriteLine($"{item.StudentId}\t{item.StudentName}\t{item.SQLServerDB}\t{item.ClassName}");
            }
        }


        #endregion

        #region 嵌套查询和子查询

        //嵌套查询
        static void NestQuery()
        {
            EFDBEntities db = new EFDBEntities();

            //查询班级编号为1或者为2的学员
            var query = from s in db.Students
                        where s.ClassId == 1 || s.ClassId == 2
                        select new { s.StudentClass.ClassName, s.StudentName, s.StudentId };
            //在上面的基础上进一步查询
            var subQuery = from s in query where s.StudentId > 100002 select s;

            //执行查询(体会Linq延迟查询的好处：将多个查询组合到一起后，生成一条SQL语句，然后执行)
            var stuList = subQuery.ToList();
            //Console.WriteLine(subQuery.ToString());
            foreach (var item in stuList)
            {
                Console.WriteLine($"{item.StudentId}\t{item.StudentName}\t{item.ClassName}");
            }
        }

        //子查询（将嵌套查询改为子查询）
        static void SubQuery()
        {
            EFDBEntities db = new EFDBEntities();
            //使用子查询
            var query = from s in (from s in db.Students
                                   where s.ClassId == 1 || s.ClassId == 2
                                   select new { s.StudentClass.ClassName, s.StudentName, s.StudentId })
                        where s.StudentId > 100002
                        select s;
            foreach (var item in query)
            {
                Console.WriteLine($"{item.StudentId}\t{item.StudentName}\t{item.ClassName}");
            }
        }

        //在select子句中使用子查询
        static void SelectSubQuery()
        {
            EFDBEntities db = new EFDBEntities();
            var query = from s in db.Students
                        select new
                        {
                            Name = s.StudentName,
                            Subject = from score in s.ScoreList
                                      select new { sql = score.SQLServerDB, cs = score.CSharp }
                        };

            foreach (var item in query)
            {
                Console.WriteLine(item.Name);
                foreach (var s in item.Subject)
                {
                    Console.WriteLine($"{s.sql},{s.cs}");
                }
                Console.WriteLine();
            }
        }

        #endregion

        #region 状态跟踪在CRUD中的影响和优化

        //状态跟踪类型测试
        static void StateTracking1()
        {

            EFDBEntities db = new EFDBEntities();
            //创建一个对象，输出当前对象的状态：Detached   
            StudentClass stuClass = new StudentClass() { ClassId = 11, ClassName = "软件11班" };
            Console.WriteLine(db.Entry(stuClass).State.ToString());

            //添加到缓存后观察状态：Added       
            db.StudentClass.Add(stuClass);
            Console.WriteLine(db.Entry(stuClass).State.ToString());

            //保存后观察状态：Unchanged
            db.SaveChanges();
            Console.WriteLine(db.Entry(stuClass).State.ToString());
        }

        //状态跟踪VS无状态跟踪
        static void StateTracking2()
        {
            EFDBEntities db = new EFDBEntities();

            //状态跟踪查询  Unchanged
            var stu1 = (from s in db.Students select s).FirstOrDefault();
            Console.WriteLine(db.Entry(stu1).State.ToString());

            //无状态查询：Detached  查询不被状态管理，好处：查询性能提升 ，返回实体不缓存。适合纯粹查询
            var stu2 = db.Students.AsNoTracking().Select(s => s).FirstOrDefault();
            Console.WriteLine(db.Entry(stu2).State.ToString());

        }

        //大批量操作需要特别注意《关闭跟踪》：关闭状态管理VS未关闭性能比较
        static void StateTracking3()
        {
            EFDBEntities db = new EFDBEntities();

            //禁用自动跟踪
            db.Configuration.AutoDetectChangesEnabled = false;

            //观察时间
            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            sw.Start();

            for (int i = 10; i < 2010; i++)
            {
                StudentClass stuClass = new StudentClass() { ClassId = i, ClassName = $"软件{i}班" };
                db.StudentClass.Add(stuClass);
            }
            Console.WriteLine("插入数据总量：" + db.SaveChanges());

            //算一下时间
            sw.Stop();
            TimeSpan timeSpan = sw.Elapsed;
            Console.WriteLine($"第一次Stopwatch总共花费{timeSpan.TotalMilliseconds}ms");

            //开启自动跟踪
            db.Configuration.AutoDetectChangesEnabled = true;

            sw.Start();
            for (int i = 2010; i < 4010; i++)
            {
                StudentClass stuClass = new StudentClass() { ClassId = i, ClassName = $"软件{i}班" };
                db.StudentClass.Add(stuClass);
            }
            Console.WriteLine("插入数据总量：" + db.SaveChanges());
            sw.Stop();
            timeSpan = sw.Elapsed;
            Console.WriteLine($"第二次Stopwatch总共花费{timeSpan.TotalMilliseconds}ms");

        }

        #endregion

        #region CRUD的标准优化

        #region Insert
        static void AddStudent()
        {
            Students student = new Students()
            {
                StudentAddress = "天津南开区",
                StudentName = "喜科堂学员20",
                StudentIdNo = "130223198910111216",
                ClassId = 2,
                Gender = "男",
                DateOfBirth = Convert.ToDateTime("1990-10-11"),
                Age = 29,
                PhoneNumber = "022-55662888"
            };

            #region 第一种写法

            //using (EFDBEntities db = new EFDBEntities())
            //{
            //    db.Entry(student).State = System.Data.Entity.EntityState.Added;//显示的告诉变化
            //    Console.WriteLine(db.SaveChanges());
            //}

            #endregion

            #region 第二种写法【最简单、最常用】

            using (EFDBEntities db = new EFDBEntities())
            {
                // db.Students.Attach(stu);//可以省略,就是将给定的实体添加到上下文
                db.Students.Add(student);
                Console.WriteLine(db.SaveChanges());
            }

            #endregion

        }

        #endregion

        #region Update

        static void UpdateStudent()
        {
            Students student = new Students()
            {
                StudentAddress = "北京市",
                StudentName = "喜科堂学员21",
                StudentIdNo = "130223198910111232",
                ClassId = 2,
                Gender = "男",
                DateOfBirth = Convert.ToDateTime("1990-10-11"),
                Age = 24,
                PhoneNumber = "13888888880",
                StudentId = 100007//注意：这个应该改成你数据库的具体值
            };

            using (EFDBEntities db = new EFDBEntities())
            {
                //db.Set<Students>().Attach(student);
                db.Entry(student).State = System.Data.Entity.EntityState.Modified;//显示的告诉当前的变化是修改，这个必须要有
                Console.WriteLine(db.SaveChanges());
            }
        }

        #endregion

        #region Delete

        static void DeleteStudent()
        {
            Students stu = new Students()//根据主键值，构造一个删除的“替身对象”
            {
                StudentId = 100010
            };
            //删除方式1
            using (EFDBEntities db = new EFDBEntities())
            {
                db.Set<Students>().Attach(stu);
                db.Entry(stu).State = EntityState.Deleted;
                Console.WriteLine(db.SaveChanges());
            };

            Students student = new Students()
            {
                StudentId = 100011
            };
            //删除方式2
            using (EFDBEntities db = new EFDBEntities())
            {
                db.Set<Students>().Attach(stu);
                db.Students.Remove(stu);
                Console.WriteLine(db.SaveChanges());
            };

        }

        #endregion

        #region Query：使用本地缓存
        static void QueryStudent()
        {
            using (EFDBEntities db = new EFDBEntities())
            {
                var stuList = from stu in db.Students select stu;
                foreach (var item in stuList)
                {
                    Console.WriteLine(item.StudentId + "\t" + item.StudentName);
                }

                //查询学生总数
                Console.WriteLine($"学生总数：{db.Students.Count()}");

                //使用缓存查询学生总数
                Console.WriteLine($"学生总数：{db.Students.Local.Count()}");

            }
        }
        #endregion

        #endregion

        #region EF执行原生SQL语句和存储过程(开发中，大家可以linq to entities结合使用，改善性能)

        //执行增删改操作：使用DbContext对象的DataBase属性，该属性支持一系列数据库操作
        static void ExecuteNonQuerySql()
        {
            string sql1 = "update Students set StudentName='张某某' where StudentId=100002";
            string sql2 = "update Students set StudentName=@StudentName where StudentId=@StudentId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@StudentName","刘某某"),
                new SqlParameter("@StudentId",100004)
            };

            using (EFDBEntities db = new EFDBEntities())
            {
                int result1 = db.Database.ExecuteSqlCommand(sql1);
                int result2 = db.Database.ExecuteSqlCommand(sql2, param);
                Console.WriteLine("第1条SQL执行结果：" + result1);
                Console.WriteLine("第2条SQL执行结果：" + result2);
            }

        }

        //调用存储过程
        static void ExecuteStoredProcedure()
        {
            SqlParameter[] param1 = new SqlParameter[]
           {
               new SqlParameter("@StudentName","王某某"),
               new SqlParameter("@StudentId",100005)
           };
            SqlParameter[] param2 = new SqlParameter[]
            {
                 new SqlParameter("@ClassId",1)
            };
            using (EFDBEntities db = new EFDBEntities())
            {
                int result1 = db.Database.ExecuteSqlCommand("execute usp_updateStu @StudentName,@StudentId", param1);
                var result2 = db.Database.SqlQuery<Students>("execute usp_selectStu @ClassId", param2);
                Console.WriteLine("存储过程usp_updateStu返回受影响行数：" + result1);
                foreach (var stu in result2)
                {
                    Console.WriteLine($"{stu.StudentId}\t{stu.StudentName}\t{stu.Age}");
                }
            }
        }

        //执行查询
        static void ExecuteQuerySql()
        {
            string sql1 = "select count(*) from Students";
            string sql2 = "select * from Students where Gender=@Gender";
            SqlParameter[] param = new SqlParameter[]
            {
                 new SqlParameter("@Gender","男")
            };

            using (EFDBEntities db = new EFDBEntities())
            {
                var stuCount = db.Database.SqlQuery<int>(sql1).ToList()[0];
                var stuList = db.Database.SqlQuery<Students>(sql2, param);
                Console.WriteLine("学生总数：" + stuCount);
                foreach (var stu in stuList)
                {
                    Console.WriteLine($"{stu.StudentId}\t{stu.StudentName}\t{stu.Age}");
                }
            }
        }

        #endregion

        #endregion

    }
}
